package busterminal.persistence.jdbc;

import busterminal.entity.Bus;
import busterminal.entity.BusImplementation;
import busterminal.entity.BusModel;
import busterminal.entity.service.PurposeType;
import busterminal.exception.BusinessException;
import busterminal.exception.DatabaseException;
import busterminal.persistence.BusDAO;
import busterminal.persistence.jdbc.proxy.BusModelProxy;
import busterminal.persistence.jdbc.proxy.BusProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Реализация интерфейса Data Access Object для автобуса
 */
public class JdbcBusDAO extends AbstractJdbcDAO<Bus> implements BusDAO {

    /**
     * Добавить автобус в базу данных
     * @param bus ссылка на автобус
     */
    @Override
    public void create(Bus bus) {
        bus.setId(getNextId());

        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("INSERT INTO Bus (Id, StateNumber, BusModelId, StartYearOfExploitation, PurposeType) VALUES (?, ?, ?, ?, ?)");
            st.setInt(1, bus.getId());
            st.setString(2, bus.getStateNumber());
            st.setInt(3, bus.getBusModel().getId());
            st.setInt(4, bus.getStartYearOfExploitation());
            st.setString(5, bus.getPurposeType().toString());
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cann't create new bus with state number " + bus.getStateNumber(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Прочитать автобус из базы данных
     * @param id идентификатор автобуса
     * @return ссылка на автобус
     */
    @Override
    public Bus read(Integer id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        Bus bus = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT Id, StateNumber, BusModelId, StartYearOfExploitation, PurposeType "
                    + "FROM Bus "
                    + "WHERE Id=" + id);
            if (rs.next()) {
                bus = new BusImplementation();
                bus.setId(rs.getInt("Id"));
                bus.setStateNumber(rs.getString("StateNumber").trim());
                BusModel busModel = new BusModelProxy();
                busModel.setId(rs.getInt("BusModelId"));
                bus.setBusModel(busModel);
                bus.setStartYearOfExploitation(rs.getInt("StartYearOfExploitation"));
                bus.setPurposeType(PurposeType.valueOf(rs.getString("PurposeType").trim()));
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read bus with Id=" + id, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if(bus == null) {
            throw new BusinessException("Bus with Id=" + id + " not found");
        }
        return bus;
    }

    /**
     * Обновить сведения о автобусе в базе данных
     * @param bus ссылка на автобус
     */
    @Override
    public void update(Bus bus) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("UPDATE Bus SET StateNumber=?, BusModelId=?, StartYearOfExploitation=?, PurposeType=? where Id=?");
            st.setString(1, bus.getStateNumber());
            st.setInt(2, bus.getBusModel().getId());
            st.setInt(3, bus.getStartYearOfExploitation());
            st.setString(4, bus.getPurposeType().toString());
            st.setInt(5, bus.getId());
            st.execute();            
        } catch (SQLException e) {
            throw new DatabaseException("Cann't update bus with Id=" + bus.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Удалить автобус из базы данных
     * @param bus ссылка на автобус
     */
    @Override
    public void delete(Bus bus) {
        delete(bus.getId());
    }

    /**
     * Удалить автобус из базы данных
     * @param id идентификатор автобуса
     */
    @Override
    public void delete(Integer id) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("DELETE FROM Bus WHERE Id=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot delete bus with Id=" + id, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }
       
    /**
     * Получить список всех автобусов из базы данных
     * @return список автобусов
     */
    @Override
    public List<Bus> findAll() {
        Connection cn = getConnection();
        java.sql.Statement st = null;
        ResultSet rs = null;
        List<Bus> busList = new ArrayList<Bus>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT Id FROM Bus");
            while (rs.next()) {
                Bus bus = new BusProxy();
                bus.setId(rs.getInt("Id"));
                busList.add(bus);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read bus list", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if(busList == null) {
            throw new BusinessException("Bus list is empty");
        }              
        return busList;
    }

    /**
     * Найти автобус по государственному номеру
     * @param stateNumber государственный номер
     * @return ссылка на автобус
     */    
    @Override
    public Bus findByStateNumber(String stateNumber) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        Bus bus = null;
        try {
            st = cn.prepareStatement("SELECT Id, StateNumber, BusModelId, StartYearOfExploitation, PurposeType "
                    + "FROM Bus "
                    + "WHERE StateNumber=?");
            st.setString(1, stateNumber);
            rs = st.executeQuery();
            if (rs.next()) {
                bus = new BusImplementation();
                bus.setId(rs.getInt("Id"));
                bus.setStateNumber(rs.getString("StateNumber").trim());
                BusModel busModel = new BusModelProxy();
                busModel.setId(rs.getInt("BusModelId"));
                bus.setBusModel(busModel);
                bus.setStartYearOfExploitation(rs.getInt("StartYearOfExploitation"));
                bus.setPurposeType(PurposeType.valueOf(rs.getString("PurposeType").trim()));
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read bus with state number " + stateNumber, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if(bus == null) {
            throw new BusinessException("Bus with state number " + stateNumber + " not found");
        }      
        return bus;
    }

    /**
     * Найти автобусы в хранилище по году начала эксплуатации
     * @param startYearOfExploitation год начала эксплуатации
     * @return список найденных автобусов
     */
    @Override
    public List<Bus> findByStartYearOfExploitation(int startYearOfExploitation) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Bus> busList = new ArrayList<Bus>();
        try {
            st = cn.prepareStatement("SELECT Id, StateNumber, BusModelId, StartYearOfExploitation, PurposeType "
                    + "FROM Bus "
                    + "WHERE StartYearOfExploitation=?");
            st.setInt(1, startYearOfExploitation);
            rs = st.executeQuery();
            while (rs.next()) {
                Bus bus = new BusImplementation();
                bus.setId(rs.getInt("Id"));
                bus.setStateNumber(rs.getString("StateNumber").trim());
                BusModel busModel = new BusModelProxy();
                busModel.setId(rs.getInt("BusModelId"));
                bus.setBusModel(busModel);
                bus.setStartYearOfExploitation(rs.getInt("StartYearOfExploitation"));
                bus.setPurposeType(PurposeType.valueOf(rs.getString("PurposeType").trim()));
                busList.add(bus);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read bus list", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if(busList == null) {
            throw new BusinessException("Buses with start year of exploitation " + startYearOfExploitation + " not found");
        }      
        return busList;
    }
}
